/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package vn.com.pps.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;
import org.springframework.jdbc.core.PreparedStatementCreatorFactory;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import vn.com.pps.entities.CourseTeacherAttendance;

/**
 *
 * @author Dai
 */
public class CourseTeacherAttendenceDAO extends BaseDAO implements ICourseTeacherAttendenceDAO {

    @Override
    public int insert(CourseTeacherAttendance courseTeacherAttendance) {

        String sql = "INSERT INTO tblcourseteacherattendance(ID,CourseID,TeacherID,Date_To,Attended_YN,Note,CreatedOn,LastModified) VALUES(?,?,?,?,?,?,?,?)";
        Object[] params = new Object[]{null, courseTeacherAttendance.getCourseID(),
            courseTeacherAttendance.getTeacherID(), courseTeacherAttendance.getDateTo(),
            courseTeacherAttendance.getAttendedYN(), courseTeacherAttendance.getNote(),
            courseTeacherAttendance.getCreatedDate(), courseTeacherAttendance.getLastModifiedDate()
        };

        PreparedStatementCreatorFactory psc
                = new PreparedStatementCreatorFactory(sql);
        psc.addParameter(new SqlParameter("ID", Types.INTEGER));
        psc.addParameter(new SqlParameter("CourseID", Types.INTEGER));
        psc.addParameter(new SqlParameter("TeacherID", Types.INTEGER));
        psc.addParameter(new SqlParameter("Date_To", Types.DATE));
        psc.addParameter(new SqlParameter("Attended_YN", Types.VARCHAR));
        psc.addParameter(new SqlParameter("Note", Types.VARCHAR));;
        psc.addParameter(new SqlParameter("CreatedOn", Types.TIMESTAMP));
        psc.addParameter(new SqlParameter("LastModified", Types.TIMESTAMP));
        psc.setReturnGeneratedKeys(true);
        KeyHolder generatedKeyHolder = new GeneratedKeyHolder();
        getJdbcTemplate().update(psc.newPreparedStatementCreator(params),
                generatedKeyHolder);
        return generatedKeyHolder.getKey().intValue();
    }

    @Override
    public int update(CourseTeacherAttendance courseTeacherAttendance) {
        String sql = "UPDATE tblcourseteacherattendance set Date_To=?, Note=?,LastModified=? WHERE ID=?";
        Object[] params = new Object[]{courseTeacherAttendance.getDateTo(),
            courseTeacherAttendance.getNote(),
            courseTeacherAttendance.getLastModifiedDate(), courseTeacherAttendance.getId()
        };

        PreparedStatementCreatorFactory psc
                = new PreparedStatementCreatorFactory(sql);

        psc.addParameter(new SqlParameter("Date_To", Types.DATE));
        psc.addParameter(new SqlParameter("Note", Types.VARCHAR));;
        psc.addParameter(new SqlParameter("LastModified", Types.TIMESTAMP));
        psc.addParameter(new SqlParameter("ID", Types.INTEGER));
        getJdbcTemplate().update(psc.newPreparedStatementCreator(params));
        return courseTeacherAttendance.getId();
    }

    @Override
    public int delete(int id) {
        String sqlDelete = "DELETE FROM  tblcourseteacherattendance WHERE ID=" + id;
        getJdbcTemplate().execute(sqlDelete);
        return 0;
    }

    @Override
    public CourseTeacherAttendance getCourseTeacherAttendance(Integer id) {
        String sqlQuery = "SELECT tblcourseteacherattendance.id,tblcourseteacherattendance.CourseID,\n"
                + "tblcourseteacherattendance.TeacherID,\n"
                + "tblcourseteacherattendance.Date_To,\n"
                + "tblcourseteacherattendance.Attended_YN,\n"
                + "tblcourseteacherattendance.Note,\n"
                + "tblcourseteacherattendance.CreatedOn,\n"
                + "tblcourseteacherattendance.LastModified,\n"
                + "tblteachers.Firstname,\n"
                + "tblteachers.Lastname,\n"
                + "tblteachers.Mobile,\n"
                + "tblcourses.CourseName\n"
                + "from tblcourseteacherattendance, tblteachers,tblcourses \n"
                + "WHERE tblcourseteacherattendance.CourseID=tblcourses.ID \n"
                + "and tblcourseteacherattendance.TeacherID=tblteachers.ID\n"
                + "AND tblcourseteacherattendance.ID=" + id;
        return (CourseTeacherAttendance) getJdbcTemplate().queryForObject(sqlQuery, new RowMapper() {
            @Override
            public Object mapRow(ResultSet rs, int i) throws SQLException {
                CourseTeacherAttendance courseTeacherAttendance = new CourseTeacherAttendance();
                courseTeacherAttendance.setId(rs.getInt("ID"));
                courseTeacherAttendance.setCourseID(rs.getInt("TeacherID"));
                courseTeacherAttendance.setTeacherID(rs.getInt("CourseID"));
                courseTeacherAttendance.setAttendedYN(rs.getString("Attended_YN"));
                courseTeacherAttendance.setDateTo(rs.getDate("Date_To"));
                courseTeacherAttendance.setLastModifiedDate(rs.getTimestamp("LastModified"));
                courseTeacherAttendance.setCreatedDate(rs.getTimestamp("CreatedOn"));
                courseTeacherAttendance.setNote(rs.getString("Note"));
                courseTeacherAttendance.setTeacherName(rs.getString("LASTNAME")+" "+rs.getString("FIRSTNAME"));
                courseTeacherAttendance.setMobile(rs.getString("MOBILE"));
                courseTeacherAttendance.setCourseName(rs.getString("CourseName"));
                return courseTeacherAttendance;
            }
        });
    }

    @Override
    public Page<CourseTeacherAttendance> getTakenRecordsByCourse(int pageNo, int pageSize, int courseID) {
        String sqlQuery = "SELECT * FROM TblCourseTeacherAttendance WHERE CourseID=" + courseID;
        String sqlQueryCount = "SELECT COUNT(*) FROM TblCourseTeacherAttendance WHERE CourseID =" + courseID;
        PaginationHelper<CourseTeacherAttendance> ph = new PaginationHelper<CourseTeacherAttendance>();
        return ph.fetchPage(
                this.getJdbcTemplate(),
                sqlQueryCount,
                sqlQuery,
                null,
                pageNo,
                pageSize, new ParameterizedRowMapper<CourseTeacherAttendance>() {
                    @Override
                    public CourseTeacherAttendance mapRow(ResultSet rs, int i) throws SQLException {
                        CourseTeacherAttendance courseTeacherAttendance = new CourseTeacherAttendance();
                        courseTeacherAttendance.setId(rs.getInt("ID"));
                        courseTeacherAttendance.setCourseID(rs.getInt("TeacherID"));
                        courseTeacherAttendance.setTeacherID(rs.getInt("CourseID"));
                        courseTeacherAttendance.setAttendedYN(rs.getString("Attended_YN"));
                        courseTeacherAttendance.setDateTo(rs.getDate("Date_To"));
                        courseTeacherAttendance.setLastModifiedDate(rs.getTimestamp("LastModified"));
                        courseTeacherAttendance.setCreatedDate(rs.getTimestamp("CreatedOn"));
                        courseTeacherAttendance.setNote(rs.getString("Note"));
                        return courseTeacherAttendance;
                    }
                });
    }

    @Override
    public int getTakenRecordCount(int courseID) {
        String sqlQuery = "SELECT COUNT(*) FROM TblCourseTeacherAttendance WHERE CourseID=" + courseID;
        return getJdbcTemplate().queryForInt(sqlQuery);
    }

    @Override
    public List<CourseTeacherAttendance> getTakenRecordsByCourse(int courseID) {
        String sqlQuery = "SELECT * FROM TblCourseTeacherAttendance WHERE CourseID=" + courseID;
        return getJdbcTemplate().query(sqlQuery, new RowMapper() {
            @Override
            public Object mapRow(ResultSet rs, int i) throws SQLException {
                CourseTeacherAttendance courseTeacherAttendance = new CourseTeacherAttendance();
                courseTeacherAttendance.setId(rs.getInt("ID"));
                courseTeacherAttendance.setCourseID(rs.getInt("TeacherID"));
                courseTeacherAttendance.setTeacherID(rs.getInt("CourseID"));
                courseTeacherAttendance.setAttendedYN(rs.getString("Attended_YN"));
                courseTeacherAttendance.setDateTo(rs.getDate("Date_To"));
                courseTeacherAttendance.setLastModifiedDate(rs.getTimestamp("LastModified"));
                courseTeacherAttendance.setCreatedDate(rs.getTimestamp("CreatedOn"));
                courseTeacherAttendance.setNote(rs.getString("Note"));
                return courseTeacherAttendance;
            }
        });

    }

    @Override
    public List<CourseTeacherAttendance> getTakenRecords(String whereClause) {
        String sqlQuery = "SELECT * FROM TblCourseTeacherAttendance WHERE " + whereClause;
        return getJdbcTemplate().query(sqlQuery, new RowMapper() {
            @Override
            public Object mapRow(ResultSet rs, int i) throws SQLException {
                CourseTeacherAttendance courseTeacherAttendance = new CourseTeacherAttendance();
                courseTeacherAttendance.setId(rs.getInt("ID"));
                courseTeacherAttendance.setCourseID(rs.getInt("TeacherID"));
                courseTeacherAttendance.setTeacherID(rs.getInt("CourseID"));
                courseTeacherAttendance.setAttendedYN(rs.getString("Attended_YN"));
                courseTeacherAttendance.setDateTo(rs.getDate("Date_To"));
                courseTeacherAttendance.setLastModifiedDate(rs.getTimestamp("LastModified"));
                courseTeacherAttendance.setCreatedDate(rs.getTimestamp("CreatedOn"));
                courseTeacherAttendance.setNote(rs.getString("Note"));
                return courseTeacherAttendance;
            }
        });
    }

}
